Imports System
Imports System.Data
Imports System.Data.OleDb

Partial Class HotFoot_FetchJobListing
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strIndustryId As String = Request.QueryString("cat1")
        Dim strIndustryDescription As String = ""
        Dim userType As String = Request.QueryString("cat2")
        'If strSkillsetId.Length = 1 Then
        'strSkillsetId = strIndustryId & "0" & strSkillsetId
        'Else
        'strSkillsetId = strIndustryId & strSkillsetId
        'End If
        'Dim strSkillsetDescription As String = ""
        Dim strResults As String = ""
        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("/access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("db/Jobs.mdb")
        'MsgBox("Data Source: " & strDataSource)
        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive

        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & "Data source=" & strDataSource

        Dim objConnection As New OleDbConnection(strConnection)
        Dim strSQL As String = "SELECT * FROM tblIndustries WHERE TRIM(industryId) = """ & Trim(strIndustryId) & """"

        Dim objCommand As New OleDbCommand(strSQL, objConnection)
        objCommand.Connection.Open()
        Dim objReader As OleDbDataReader
        Try
            objReader = objCommand.ExecuteReader()
            While objReader.Read()
                strIndustryDescription = Trim(objReader.Item("industryDescription"))
                Exit While
            End While
            objReader.Close()
            'MsgBox("Industry: " & strIndustryDescription)
            strSQL = "SELECT * FROM tblJobs WHERE TRIM(jobIndustry) = """ & Trim(strIndustryDescription) & """"
            'MsgBox("SQL: " & strSQL)
            objCommand.CommandText = strSQL
            objCommand.CommandType = CommandType.Text
            objCommand.Connection = objConnection
            strResults = "{""Results"": {""records"": ["
            objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If objReader.HasRows Then
                While objReader.Read()
                    strResults &= "{""JobId"":"""
                    strResults &= Trim(objReader.Item("jobId")) & ""","
                    strResults &= """JobTitle"":"""
                    strResults &= Trim(objReader.Item("jobSpecTitle")) & ""","
                    strResults &= """City"":"""
                    strResults &= Trim(objReader.Item("jobCity")) & ""","
                    strResults &= """Status"":"""
                    strResults &= Trim(objReader.Item("jobStatus")) & ""","
                    strResults &= """PostedDate"":"""
                    strResults &= Trim(objReader.Item("jobDate"))
                    strResults &= """}"
                    strResults &= ","
                End While
                strResults = strResults.Substring(0, strResults.Length - 1)
                strResults &= "]}}" & userType & "@"
            Else
                strResults = "not found@"
            End If
            objReader.Close()
            'MsgBox("Skill set: " & strSkillsetDescription)
            objConnection.Close()
            objConnection.Dispose()

            'MsgBox("Results: " & strResults)
            Response.Write(strResults)
        Catch ex As Exception
            'objReader.Close()
            objConnection.Close()
            objConnection.Dispose()
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub
End Class
